/*
Navicat MySQL Data Transfer

Source Server         : my
Source Server Version : 50610
Source Host           : localhost:3306
Source Database       : xingcheng

Target Server Type    : MYSQL
Target Server Version : 50610
File Encoding         : 65001

Date: 2017-09-04 12:20:17
*/

SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for `examresult`
-- ----------------------------
DROP TABLE IF EXISTS `examresult`;
CREATE TABLE `examresult` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `exam_date` date DEFAULT NULL,
  `score` float(6,2) NOT NULL,
  `st_id` int(10) unsigned DEFAULT NULL COMMENT '学生编号',
  `sb_id` int(10) unsigned DEFAULT NULL COMMENT '科目编号',
  PRIMARY KEY (`id`),
  KEY `fk_stu` (`st_id`),
  KEY `fk_subject` (`sb_id`),
  CONSTRAINT `fk_stu` FOREIGN KEY (`st_id`) REFERENCES `student` (`stuno`),
  CONSTRAINT `fk_subject` FOREIGN KEY (`sb_id`) REFERENCES `subject` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of examresult
-- ----------------------------
INSERT INTO examresult VALUES ('1', '2017-09-01', '90.00', '1', '3');
INSERT INTO examresult VALUES ('2','2017-09-01','89.00','2','4');
INSERT INTO examresult VALUES ('3','2017-09-02','89.00','3','3');
INSERT INTO examresult VALUES ('4','2017-09-02','89.00','10','18');
INSERT INTO examresult VALUES ('5','2017-09-02','89.00','11','19');
INSERT INTO examresult VALUES ('6','2017-09-02','89.00','12','20');
-- ----------------------------
-- Table structure for `grade`
-- ----------------------------
DROP TABLE IF EXISTS `grade`;
CREATE TABLE `grade` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '年级编号',
  `gname` varchar(100) NOT NULL COMMENT '年级名称',
  PRIMARY KEY (`id`),
  UNIQUE KEY `gname` (`gname`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of grade
-- ----------------------------
INSERT INTO grade VALUES ('5', 'Java');
INSERT INTO grade VALUES ('1', 'S1');
INSERT INTO grade VALUES ('2', 'S2');
INSERT INTO grade VALUES ('3', 'Y2');
INSERT INTO grade VALUES ('7','mysql7');
INSERT INTO grade VALUES ('8','mysql8');
INSERT INTO grade VALUES ('9','mysql9');

-- ----------------------------
-- Table structure for `student`
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `stuno` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `sname` varchar(40) NOT NULL COMMENT '姓名',
  `sex` enum('男','女','妖','人妖') DEFAULT '男' COMMENT '性别',
  `telephone` varchar(15) DEFAULT NULL COMMENT '电话',
  `qq` varchar(20) DEFAULT NULL COMMENT 'QQ号',
  `wechat` varchar(50) DEFAULT NULL COMMENT '微信',
  `gradeid` int(20) unsigned NOT NULL COMMENT '年级',
  `enterdate` date DEFAULT NULL COMMENT '入学日期',
  `fromcity` varchar(100) DEFAULT NULL COMMENT '从那个城市来的',
  `birthday` date DEFAULT NULL COMMENT '出生日期',
  `address` varchar(400) DEFAULT NULL COMMENT '地址',
  PRIMARY KEY (`stuno`),
  KEY `fk_grade2` (`gradeid`),
  CONSTRAINT `fk_grade2` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;
ALTER TABLE examresult ADD CONSTRAINT fk_stunum FOREIGN KEY(st_id) REFERENCES student(stuno);
-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO student VALUES ('1', '王武', '男', '131800433221', '14574934', null, '1', null, '阜阳', null, '阜阳');
INSERT INTO student VALUES ('2', '王莽', '男', '131800433221', null, null, '2', '2017-03-10', '合肥', null, null);
INSERT INTO student VALUES ('3', '李星', '女', '131800433221', '1949944', null, '3', '2017-04-10', '合肥', null, '阜阳');
INSERT INTO student VALUES ('5', '李八', '女', '131800433221', '666666', null, '3', '2017-04-20', '六安', null, '阜阳颖州区');
INSERT INTO student VALUES ('6', '牛三', '妖', '13590812397', '14574934', '14574934', '2', '2017-04-21', '六安', null, null);
INSERT INTO student VALUES ('7', '丁玉锁', '男', null, null, null, '5', null, '阜阳', '1995-05-01', null);
INSERT INTO student VALUES ('8', '丁钢锁', '男', null, null, null, '5', null, '阜阳', '1997-05-01', null);
INSERT INTO student VALUES ('10', '张三', '男', null, null, null, '7', null, '芜湖', '1993-05-01', null);
INSERT INTO student VALUES ('11', '张三', '男', null, null, null, '8', null, '庐江', '1997-09-02', null);
INSERT INTO student VALUES ('12', '张三', '男', null, null, null, '9', null, '合肥', '1999-07-01', null);
INSERT INTO student VALUES ('13', '张三', '男', null, null, null, '9', null, '合肥', '1999-01-31', null);
INSERT INTO student VALUES ('14', '张a', '男', null, null, null, '9', null, '合肥', '1999-03-31', null);
-- ----------------------------
-- Table structure for `subject`
-- ----------------------------
SELECT * FROM student WHERE fromcity<>'六安'AND sex='男';
SELECT * from student WHERE LAST_DAY(birthday)=birthday;

DROP TABLE IF EXISTS `subject`;
CREATE TABLE `subject` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '课程编号',
  `subjectname` varchar(100) NOT NULL,
  `classhour` int(10) unsigned DEFAULT NULL,
  `gradeid` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_grade` (`gradeid`),
  CONSTRAINT `fk_grade` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of subject
-- ----------------------------
INSERT INTO subject VALUES ('1', 'java 基础', '16', '5');
INSERT INTO subject VALUES ('3', 'java oop', '20', '2');
INSERT INTO subject VALUES ('4', 'java高级特性', '8', '5');
INSERT INTO subject VALUES ('6', '利用html+css设计网页', '19', '3');
INSERT INTO subject VALUES ('14', '使用jquery实现网页特效', '12', '5');
INSERT INTO subject VALUES ('15', '数据库编程', '12', '5');
INSERT INTO subject VALUES ('16', 'java web编程', '16', '5');
INSERT INTO subject VALUES ('17', '深入mybatis', '10', '5');
INSERT INTO subject VALUES ('18', 'spring详解', '8', '5');
INSERT INTO subject VALUES ('19', 'struts2初步', '3', '5');
INSERT INTO subject VALUES ('20', 'hibernate浅窥', '2', '5');
INSERT INTO subject VALUES ('21', 'mysql', '2', '7');

INSERT INTO subject VALUES ('22', 'java大数据', '2', '8');

INSERT INTO subject VALUES ('23', 'c语言', '2', '9');
-- -- 课后练习1
-- SELECT  exam_date AS 考试时间,score AS 考试成绩,st_id AS 学员编号,sb_id AS 课程编号 FROM examresult
-- WHERE st_id=ANY(SELECT stuno FROM student WHERE gradeid=2);
-- -- 课后练习2
-- SELECT exam_date AS 考试时间,score AS 考试成绩,st_id AS 学员编号,sb_id AS 课程编号 ,sname AS 姓名  FROM examresult,student
-- WHERE examresult.st_id=student.stuno;
-- -- 课后练习3
-- 参加java oop的应到人数
-- SELECT COUNT(*) FROM student WHERE gradeid=ANY(SELECT gradeid FROM `subject` WHERE subjectname='java oop'  );
-- 参加 java oop的实际人数
-- SELECT count(*) FROM examresult WHERE sb_id=(SELECT id FROM `subject` WHERE subjectname='java oop' );
-- 
-- SELECT sb_id,AVG(score) FROM examresult group by sb_id;
-- SELECT SUM(classhour),gradeid FROM `subject` GROUP BY gradeid order by SUM(classhour) ASC;
-- 
-- SELECT COUNT(*),avg(score) FROM examresult WHERE sb_id=3;
-- SELECT st_id,score from examresult WHERE exam_date='2016-2-17' ORDER BY score DESC LIMIT 5;
-- SELECT * FROM student WHERE sex='女' ORDER BY birthday DESC LIMIT 1,6;
-- SELECT MAX(score),MIN(score),avg(score) FROM examresult WHERE  exam_date='2016-2-17' ;
-- 
-- 
-- SELECT * FROM student WHERE birthday=(SELECT max(birthday) FROM student);
-- 
-- SELECT * FROM student WHERE stuno in(SELECT st_id FROM examresult WHERE sb_id NOT IN(SELECT id FROM `subject` WHERE subjectname='java 基础' ));
-- 
-- SELECT stuno AS 学号,sname AS 姓名,telephone AS 电话号码,qq AS qq号,score,exam_date FROM student,examresult WHERE stuno in(SELECT st_id FROM examresult WHERE sb_id IN(SELECT id FROM `subject` WHERE subjectname='java 基础' ))AND student.stuno=examresult.st_id  ;
-- 
-- SELECT subjectname FROM `subject` WHERE gradeid in( SELECT id FROM grade WHERE gname='S1');
-- 
-- show tables;

 找出3个月之前入学的学生
 select * from student 
  WHERE enterdate < adddate(now(),INTERVAL -3 MONTH)
  order by gradeid desc,enterdate desc 
 

 select stuno,sname,DATE_FORMAT(enterdate,'%Y/%M/%D') from student 
  WHERE enterdate < adddate(now(),INTERVAL -3 MONTH)
  order by gradeid desc,enterdate desc 



--有问题的
insert into student(sname,gradeid,birthday)
 values('丁钢锁',5,'05011997');

insert into student(sname,gradeid,birthday)
 values('丁钢锁',5,str_to_date('05011997','%m%d%Y'));




select  * from subject order by id desc limit 4,2



CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `createtime` timestamp NULL default CURRENT_TIMESTAMP
) 

insert into t1(id) values(110);

select * from student;

9-6
insert into student(sname,sex,telephone,gradeid,enterdate,birthday)
 values('刘嘉玲','女','1488483982',5,now(),'1978-2-6');

update student set fromcity='苏州' where fromcity is null;

update student set telephone='13864399081' ;

update student set telephone=concat('1',stuno,'3838388',stuno) ;

--总共有多少学生
select count(*)as 总数 from student;
--有多少学生提供了QQ
select count(qq)as 总数 from student;
select * from student order by qq ASC
select * from student order by qq desc

update student set birthday=DATE_ADD('1981-2-2',INTERVAL stuno MONTH)
 where birthday is null;

select min(birthday) from student;
-- 找出六安地区最小的学生生日
select max(birthday) from student where fromcity='六安';

select min(birthday),max(birthday),count(stuno)
 from student where fromcity='六安';

select min(birthday),max(birthday),count(stuno)
 from student 
--不合理
select sum(birthday) from student;
--找出课程号是3的课程，考了几次，平均分是多少？

--利用查询语句创建表
create table  tongxunlu
AS
 select sname,telephone 
 from student;

select * from tongxunlu


select * from examresult where exam_date='2016-2-17'


select birthday from student where sname='李斯文';


select * from student where birthday>'1985-09-02';
update student set birthday='1981-2-2' where sname='李斯文';


select * from student where birthday >(
  select birthday from student where sname='李斯文'
)


select * from student where birthday >(
  select birthday from student where sname='李文'
)

--找出年龄最小的学生信息（所有信息）
select * from student order by birthday desc limit 1;--不全对


SELECT * FROM student WHERE birthday=(SELECT max(birthday) FROM student);

--找出比合肥地区的学生年龄都要大的学生
  
 select * from student where birthday>(select birthday from student where fromcity='合肥')

 --找出成绩不低于85的学生全部信息
select * from examresult where score>=85

select * from student where stuno in(
select st_id from examresult where score>=85
)

-查询 参加过  java基础  考试的学生名单（）

SELECT * from student
 where stuno in
(SELECT st_id from  examresult WHERE 
sb_id in(SELECT id from subject WHERE subjectname='java 基础') )

--java基础  最近一次  考试的学生信息
SELECT * from student
 where stuno in
(SELECT st_id from  examresult WHERE 
sb_id in(SELECT id from subject WHERE subjectname='java 基础') 
 and exam_date =(
	    select max(exam_date) from examresult 
      where sb_id=(SELECT id from subject WHERE subjectname='java 基础')
  )
)



SELECT * from student
 where stuno not in
(SELECT st_id from  examresult WHERE 
sb_id in(SELECT id from subject WHERE subjectname='java 基础') )


select * from student 
where fromcity not in('阜阳')


--找出html的课程编号
select id from subject where subjectname='利用html+css设计网页';

select st_id from examresult where sb_id=(
 select id from subject where subjectname='利用html+css设计网页'
)
--没有考过的
select * from student 
where stuno
 not in 
(
 select st_id from examresult where sb_id=(
 select id from subject where subjectname='利用html+css设计网页'
)
)


-- 3：找出所有80后的学生，即1980-1989之间出生的学生
SELECT * FROM student WHERE YEAR(birthday) BETWEEN 1980 AND 1989;

-- 4:找出来自 合肥 地区，有考试低于60的学生信息
SELECT * FROM student WHERE fromcity='合肥' AND stuno IN(SELECT st_id FROM examresult WHERE score<60);

select TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,COLUMN_TYPE,COLUMN_COMMENT from information_schema.columns where TABLE_SCHEMA='chapter02_1';


s2年级的学生
SELECT * FROM student WHERE gradeid IN(SELECT id FROM grade WHERE gname='S2')

SELECT st_id AS 学生编号,sb_id AS 科目编号,score AS 考试成绩,exam_date AS 考试时间 FROM examresult
WHERE EXISTS(
	SELECT stuno FROM student WHERE gradeid IN(SELECT id FROM grade WHERE gname='S2')
)AND sb_id IN(SELECT id FROM subject WHERE gradeid in(SELECT id FROM grade WHERE gname='S2'));

-- 各个性别有多少人，最大的生日，最小的生日
SELECT sex,count(*) AS 人数,MAX(birthday) AS 最小的生日,MIN(birthday) AS 最大的生日 FROM student
GROUP BY sex;

-- 统计各个地区，各种性别的人数
SELECT fromcity AS 地区,sex AS 性别,count(*) 人数 FROM student
GROUP BY fromcity,sex;

-- 去掉重复
SELECT DISTINCT fromcity FROM student;

SELECT fromcity FROM student GROUP BY fromcity;

-- 方法一：伪表，where只能筛选原始数据，分过组的会保错
SELECT * FROM (
		SELECT sb_id,AVG(score) AS 平均分 FROM examresult
		GROUP BY sb_id
)t
WHERE t.平均分>=85;

-- 方法二：having
SELECT sb_id,AVG(score) AS '平均分' FROM examresult
		GROUP BY sb_id
		HAVING 平均分>=85


SELECT fromcity AS 地区,COUNT(*) AS 人数 FROM student
GROUP BY fromcity
HAVING COUNT(*)>2;

SELECT stuno,sname FROM student
UNION 
SELECT id,subjectname FROM subject;


SELECT s.sname AS 姓名,e.score AS 分数,e.st_id AS 学号 FROM student s
LEFT JOIN examresult e ON s.stuno=e.st_id;

SELECT g.id AS 年级,s.subjectname AS 课程 FROM grade g
LEFT JOIN subject s on g.id=s.gradeid;


1:查询累计考试次数 超过3次的学生信息（全部）。
SELECT s.*,COUNT(*) FROM student s
INNER JOIN examresult e ON s.stuno=e.st_id
GROUP BY e.st_id
HAVING COUNT(e.st_id)>3;


2：查询有过补考的学生信息（同一门课超过2次，认为是补考）
SELECT * FROM student WHERE stuno IN(SELECT st_id FROM examresult GROUP BY sb_id,st_id HAVING count(*)>2);

3：最近半年内入学的学生
SELECT * FROM student WHERE enterdate>=ADDDATE(CURDATE(),INTERVAL -6 MONTH);

4: 查询年级信息，以及该年级的课程数，没有课程的也要显示，对应的课程数目为0
-- SELECT t.id AS 年级编号,COUNT(*) AS 课程数,'' AS 课程名 FROM(
-- SELECT g.id,g.gname FROM grade g
-- INNER JOIN subject s ON g.id=s.gradeid)t GROUP BY t.id
-- UNION 
-- SELECT id,0,'' FROM grade WHERE id NOT IN(SELECT gradeid FROM subject)
-- UNION
-- SELECT g.id,g.gname,s.subjectname FROM grade g
-- LEFT JOIN subject s ON g.id=s.gradeid
--方法一：
SELECT g.id AS 年级编号,g.gname AS 年级名称,COUNT(s.gradeid) AS 课程数 
FROM grade AS g
LEFT JOIN subject AS s ON(s.gradeid=g.id) 
GROUP BY g.id

--方法二：
SELECT id AS 年级,COUNT(gradeid) AS 课程数 FROM(
	SELECT g.id,s.gradeid FROM grade AS g LEFT JOIN subject AS s ON g.id=s.gradeid
)t GROUP BY id

--方法三：子查询作为结果集一部分，每次返回不能超过一个记录
SELECT g.*,(SELECT count(*) FROM subject s WHERE s.gradeid=g.id) AS 课程数 FROM grade g;

--找出电话以13/14/15开头的学生
SELECT * FROM student WHERE telephone REGEXP '^1[345]';

--找出各个年级最小的学生的全部信息
#方法一：
SELECT * from student
WHERE (gradeid,birthday) IN (SELECT gradeid,MAX(birthday) FROM student GROUP BY gradeid);

#方法二：
SELECT s.* FROM student s 
INNER JOIN (SELECT gradeid,MAX(birthday)AS maxbir FROM student GROUP BY gradeid)t
ON s.gradeid=t.gradeid AND s.birthday=t.maxbir;

SELECT * FROM examresult
BEGIN;
INSERT INTO examresult(exam_date,score,st_id,sb_id)
VALUES(NOW(),99,15,22),(NOW(),80,16,22),(NOW(),50,14,22);
ROLLBACK;
COMMIT;


CREATE VIEW view_studentinfo
AS
SELECT sname,qq,(YEAR(NOW())-YEAR(birthday)) AS age FROM student;

SELECT * FROM view_studentinfo;
DROP VIEW IF EXISTS view_studentinfo;

SELECT st.sname,su.subjectname,avg(score)FROM examresult e
INNER JOIN student st ON e.st_id=st.stuno
INNER JOIN subject su  ON e.sb_id=su.id
GROUP BY st.stuno,sb_id;

SELECT (SELECT sname FROM student WHERE e.st_id=stuno)st,(SELECT subjectname FROM subject WHERE e.sb_id=id)su,AVG(score) FROM examresult e
GROUP BY st_id,sb_id;




--设计高分成绩单视图，只包含成绩在80分以上的学生信息，包括科目名称以及成绩
DROP VIEW IF EXISTS view_highscore;
CREATE VIEW view_highscore
AS
SELECT stuno AS 学号,sname AS 姓名,student.gradeid AS 年级,subjectname AS 科目,score AS 成绩
FROM student,subject,examresult
WHERE (student.stuno,subject.id,score)IN(
SELECT st_id,sb_id,score FROM examresult WHERE score>=80)GROUP BY sname,subjectname,score;

-- SELECT * FROM view_highscore;





